﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Management;
using System.Windows.Forms;

namespace intelliPaper.DB.AccessUtil
{
    /// <summary>
    /// 数据库连接类，用来连接Access数据库
    /// 包括数据连接和事务
    /// 以及所有与数据库的交互函数
    /// </summary>
    class DBConnection
    {

        /// <summary>
        /// 数据库连接实体
        /// </summary>
        public  OleDbConnection DBConn = null;

        /// <summary>
        /// 事务实体
        /// </summary>
        OleDbTransaction Transaction = null;

        /// <summary>
        /// 获得数据库连接
        /// </summary>
        /// <returns>通过连接字符串得到的数据库连接</returns>
        public  OleDbConnection getConn()
        {
            try
            {

                OleDbConnection odco = new OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["accesssedbconn"].ConnectionString);
                return odco;
            }
            catch (OleDbException ode)
            {
                MessageBox.Show(ode.Message);
                throw new Exception();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw new Exception();
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private void CloseConn()
        {
            if (DBConn.State == ConnectionState.Open)
            {
                DBConn.Close();
                DBConn = null;
            }
        }

        /// <summary>
        /// 获得数据库连接以及事务
        /// </summary>
        public void GetConn()
        {
            try
            {
                //获得数据库连接
                DBConn = getConn();
                //打开数据库连接
                DBConn.Open();
                //获得事务
                Transaction = DBConn.BeginTransaction();
            }
            catch (OleDbException ode)
            {
                MessageBox.Show(ode.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            if (Transaction != null)
            {
                Transaction.Commit();
            }
            Transaction = null;
            //关闭数据库连接
            CloseConn();
        }

        /// <summary>
        /// 执行SQL 语句，改，删
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <returns>是否成功</returns>
        public bool ExecuteSql(string sqlString)
        {
            try
            {
                OleDbConnection odcn  = getConn();
                odcn.Open();
                //创建数据库命令
                OleDbCommand odco = new OleDbCommand(sqlString,odcn );
                //影响的行数，大于零证明执行成功
                int result = odco.ExecuteNonQuery();
                odcn.Close();
                return result > 0 ? true : false;
            }
            catch (OleDbException ode)
            {
                MessageBox.Show(ode.Message);
                throw new Exception();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw new Exception();
            }
        }

        /// <summary>
        /// 向数据库中添加单条记录
        /// </summary>
        /// <param name="arr">列名与值的对</param>
        /// <param name="tableName">所要添加到的表的名字</param>
        /// <returns>是否成功</returns>
        public bool Insert(Dictionary<string, object> arr, string tableName)
        {
            try
            {
                if (Transaction == null)
                    GetConn();
                string sql;
                string sqll = "insert into " + tableName + "(";
                string sqlr = ") values (";

                OleDbCommand odco = new OleDbCommand();
                odco.Connection = DBConn;
                odco.Transaction = Transaction;

                //生成SQL语句，为语句添加参数
                foreach (KeyValuePair<string, object> pair in arr)
                {
                    sqll += pair.Key + ",";
                    sqlr += "@" + pair.Key + ",";
                    
                    //OleDbParameter odp = new OleDbParameter("@" + pair.Key, pair.Value.GetType());
                    //OleDbParameter odp = new OleDbParameter("@" + pair.Key, pair.Value);
                    //odp.Value = pair.Value;
                    OleDbParameter odp = new OleDbParameter();
                    odp.ParameterName = "@" + pair.Key;
                    if (pair.Value.GetType() == typeof(DateTime))
                    {
                        odp.Value = Convert.ToDateTime(pair.Value.ToString());
                    }
                    else
                    {
                        odp.Value = pair.Value;
                    }
                    odco.Parameters.Add(odp);
                }

                //将两个SQL语句拼接起来
                sql = sqll.Substring(0, sqll.Length - 1) + sqlr.Substring(0, sqlr.Length - 1) + ")";
                //将SQL语句赋给odco
                odco.CommandText = sql;

                bool result = Convert.ToBoolean(odco.ExecuteNonQuery());

                return result;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                //如果有异常，回滚数据库
                Transaction.Rollback();
                Transaction = null;
                if (DBConn != null)
                {
                    CloseConn();
                }
                return false;
            }

        }

        /// <summary>
        /// 取出前一条语句的自动编号的关键字
        /// </summary>
        /// <returns>编号</returns>
        public int GetIdentity()
        {
            OleDbCommand odco = new OleDbCommand("select @@IDENTITY", DBConn, Transaction);
            int result = Convert.ToInt32(odco.ExecuteScalar());
            return result;
        }

        /// <summary>
        /// 向数据库中添加多条记录
        /// </summary>
        /// <param name="arr">列名与值的对的列表</param>
        /// <param name="tableName">所要添加到的表的名字</param>
        /// <returns>是否成功</returns>
        public bool Insert(List<Dictionary<string, object>> arrs, string tableName)
        {
            try
            {
                if (Transaction == null)
                    GetConn();
                string sql;
                string sqll;
                string sqlr;
                OleDbCommand odco = new OleDbCommand();
                odco.Connection = DBConn;
                odco.Transaction = Transaction;

                foreach (Dictionary<string, object> pairs in arrs)
                {
                    sqll = "insert into " + tableName + "(";
                    sqlr = ") values (";

                    //生成SQL语句，为语句添加参数
                    foreach (KeyValuePair<string, object> pair in pairs )
                    {
                        sqll += pair.Key + ",";
                        sqlr += "@" + pair.Key + ",";
                        OleDbParameter odp = new OleDbParameter();
                        odp.ParameterName = "@" + pair.Key;
                        if (pair.Value.GetType() == typeof(DateTime))
                        {
                            odp.Value = Convert.ToDateTime(pair.Value.ToString());
                        }
                        else
                        {
                            odp.Value = pair.Value;
                        }
                        odco.Parameters.Add(odp);
                    }

                    //将两个SQL语句拼接起来
                    sql = sqll.Substring(0, sqll.Length - 1) + sqlr.Substring(0, sqlr.Length - 1) + ")";
                    //将SQL语句赋给odco
                    odco.CommandText = sql;

                    if (!Convert.ToBoolean(odco.ExecuteNonQuery()))
                    {
                        throw new Exception("添加失败");
                    }
                    odco.Parameters.Clear();

                }
                return true;
            }
            catch (Exception ex)
            {
                //如果有异常，回滚数据库
                Transaction.Rollback();
                Transaction = null;
                if (DBConn != null)
                {
                    CloseConn();
                }
                MessageBox.Show(ex.Message);
                return false;
            }

        }

        /// <summary>
        /// 查找记录，并返回一个list
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <returns>返回的记录的集合</returns>
        public List<Dictionary<string, object>> SelectToList(string sqlString)
        {
            try
            {
                DBConn = getConn();
                OleDbDataAdapter odda = new OleDbDataAdapter(sqlString, DBConn);
                List<Dictionary<string, object>> arr = new List<Dictionary<string, object>>();
                if (odda == null)
                {
                    throw new Exception("没有取回任何数据");
                }
                DataTable dt = new DataTable();
                odda.Fill(dt);
                foreach (DataRow rw in dt.Rows)
                {
                    Dictionary<string, object> ar = new Dictionary<string, object>();
                    foreach (DataColumn dc in dt.Columns)
                    {
                        ar.Add(dc.ColumnName.ToLower (), rw[dc.ColumnName]);
                    }
                    arr.Add(ar);
                }
                return arr;
            }
            catch (Exception ex)
            {
                if (DBConn != null)
                {
                    CloseConn();
                }
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 修改记录
        /// </summary>
        /// <param name="values">要修改的字段的名字和值对</param>
        /// <param name="conditions">修改哪些记录</param>
        /// <param name="tableName">修改哪个表</param>
        /// <returns>是否成功</returns>
        public bool Update(Dictionary<string, object> values,Dictionary <string ,object > conditions, string tableName)
        {
            try
            {
                if (Transaction == null)
                {
                    GetConn();
                }
                string sqlString = "update " + tableName + " set ";
                OleDbCommand odco = new OleDbCommand();
                odco.Connection = DBConn;
                odco.Transaction = Transaction;

                foreach (KeyValuePair<string, object> pair in values)
                {
                    sqlString += pair.Key + "=@" + pair.Key + ",";
                    OleDbParameter odp = new OleDbParameter();
                    odp.ParameterName = "@" + pair.Key;
                    if (pair.Value.GetType() == typeof(DateTime))
                    {
                        odp.Value = Convert.ToDateTime(pair.Value.ToString());
                    }
                    else
                    {
                        odp.Value = pair.Value;
                    }
                    odco.Parameters.Add(odp);
                }

                sqlString = sqlString.Substring(0, sqlString.Length - 1) + " where ";

                foreach (KeyValuePair<string, object> pair in conditions)
                {
                    sqlString += pair.Key + "=@" + pair.Key + " and ";
                    OleDbParameter odp = new OleDbParameter();
                    odp.ParameterName = "@" + pair.Key;
                    if (pair.Value.GetType() == typeof(DateTime))
                    {
                        odp.Value = Convert.ToDateTime(pair.Value.ToString());
                    }
                    else
                    {
                        odp.Value = pair.Value;
                    }
                    odco.Parameters.Add(odp);
                }

                sqlString = sqlString.Substring(0, sqlString.Length - 4);

                odco.CommandText = sqlString;

                return odco.ExecuteNonQuery() > 0 ? true : false;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                Transaction.Rollback();
                Transaction = null;
                if (DBConn != null)
                {
                    CloseConn();
                }
                return false;

            }
        }

        /// <summary>
        /// 查找返回数据表
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public DataTable Select(string sqlString)
        {
            try
            {
                OleDbConnection odco=getConn ();
                OleDbDataAdapter odda = new OleDbDataAdapter(sqlString,odco);
                DataTable dt = new DataTable();
                odda.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                if (DBConn != null)
                {
                    CloseConn();
                }
                MessageBox.Show(ex.Message);
                return null;
            }
        }
    }

}
